set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_fastest
select
     in_from_code
    ,update_date
    ,in_from_provider_code
    ,in_from_city_code
    ,in_from_area_code
    ,out_to_provider_code
    ,out_to_city_code
    ,out_to_area_code
    ,out_to_code
    ,in_branch_id
    ,main_id
    ,out_branch_id
    ,in_collect_code
    ,in_to_code
    ,start_center_code
    ,search_type
    ,start_network_code
    ,in_from_regional_code
    ,in_from_regional_desc
    ,in_from_financial_center_code
    ,in_from_financial_center_desc
    ,in_from_provider_desc
    ,in_from_city_desc
    ,in_from_area_desc
    ,in_collect_name
    ,in_from_name
    ,in_to_name
    ,in_edge_latest_warehousing
    ,in_edge_planned_departure
    ,in_edge_planned_arrival
    ,in_edge2_planned_departure
    ,in_edge2_planned_arrival
    ,branch_in_span_days
    ,branch_in_total_time
    ,is_main_route
    ,transfer_type
    ,num_of_transfer
    ,whole_route
    ,e1_center_flow
    ,e1_line_code
    ,e1_planned_departure
    ,e1_runtime
    ,e1_planned_arrival
    ,e1_e2_stop_time
    ,e2_center_flow
    ,e2_line_code
    ,e2_planned_departure
    ,e2_runtime
    ,e2_planned_arrival
    ,e2_e3_stop_time
    ,e3_center_flow
    ,e3_line_code
    ,e3_planned_departure
    ,e3_runtime
    ,e3_planned_arrival
    ,e3_e4_stop_time
    ,e4_center_flow
    ,e4_line_code
    ,e4_planned_departure
    ,e4_runtime
    ,e4_planned_arrival
    ,e4_e5_stop_time
    ,e5_center_flow
    ,e5_line_code
    ,e5_planned_departure
    ,e5_runtime
    ,e5_planned_arrival
    ,e5_e6_stop_time
    ,e6_center_flow
    ,e6_line_code
    ,e6_planned_departure
    ,e6_runtime
    ,e6_planned_arrival
    ,e6_e7_stop_time
    ,e1_end_code
    ,e2_end_code
    ,e3_end_code
    ,e4_end_code
    ,e5_end_code
    ,e6_end_code
    ,transfer_total_span_days
    ,transfer_total_time_use
    ,main_total_span_days
    ,main_total_time_use
    ,end_center
    ,end_center_code
    ,start_center
    ,out_from_code
    ,out_collect_code
    ,out_to_regional_desc
    ,out_to_regional_code
    ,out_to_financial_center_desc
    ,out_to_financial_center_code
    ,out_to_provider_desc
    ,out_to_city_desc
    ,out_to_area_desc
    ,out_collect_name
    ,out_to_name
    ,out_from_name
    ,out_has_collect
    ,out_edge_planned_departure
    ,out_edge_planned_arrival_time
    ,out_edge2_planned_departure
    ,out_edge2_planned_arrival_time
    ,out_edge2_latest_warehouse_time
    ,out_edge_latest_warehouse_time
    ,out_edge_span_days_sign
    ,out_edge2_span_days_sign
    ,out_edge_deadline_sign_time
    ,out_edge2_deadline_sign_time
    ,branch_out_span_days
    ,branch_out_total_time
    ,effective_date
    ,expiration_date
    ,branch_in_center_stop_time
    ,center_branch_out_stop_time
    ,branch_in_center_span_days
    ,center_branch_out_span_days
    ,is_circuitous
    ,out_edge_span_days_arrive
    ,out_edge2_span_days_arrive
    ,in_nodes
    ,out_nodes
    ,total_days_use
    ,total_time_use
    ,total_days_t
    ,total_nodes
    ,working_days
    ,extra_in_collect_code
    ,extra_in_collect_name
    ,extra_in_line_name
    ,extra_in_latest_warehousing
    ,extra_in_planned_departure
    ,extra_in_planned_arrival
    ,extra_out_collect_code
    ,extra_out_collect_name
    ,extra_out_line_name
    ,extra_out_latest_warehousing
    ,extra_out_planned_departure
    ,extra_out_planned_arrival
    ,in_network_line_name
    ,in_collect_line_name
    ,out_network_line_name
    ,out_collect_line_name
    ,town_plus_time
    ,branch_in_ship_time
    ,branch_in_ship_span_days
    ,branch_out_ship_time
    ,branch_out_ship_span_days
    ,all_line_name
    ,out_town_plus_time
    ,in_edge_run_time
    ,in_edge_span_days
    ,in_edge2_run_time
    ,in_edge2_span_days
    ,in_edge3_run_time
    ,in_edge3_span_days
    ,out_edge_run_time
    ,out_edge_span_days
    ,out_edge2_run_time
    ,out_edge2_span_days
    ,out_edge3_run_time
    ,out_edge3_span_days
    ,e1_span_days
    ,e2_span_days
    ,e3_span_days
    ,e4_span_days
    ,e5_span_days
    ,e6_span_days
    ,delivery_time
    ,route_flow
    ,in_edge2_latest_warehousing
    ,e1_latest_warehousing
    ,e2_latest_warehousing
    ,e3_latest_warehousing
    ,e4_latest_arrival_time
    ,e5_latest_arrival_time
    ,e6_latest_arrival_time
    ,start_taking_shift
    ,end_send_shift
    ,warehouse_end_time
    ,reserve_1
    ,reserve_2
    ,route_contain_main_line
    ,direct_route_type
    ,in_line_day
    ,out_line_day
    ,in_ship_span_days
    ,in_manage_region_code
    ,in_manage_region_name
    ,out_manage_region_code
    ,out_manage_region_name
    ,latest_warehousing_time_quantum
    ,branch_in_total_all_time
    ,main_total_all_time
    ,branch_out_total_all_time
    ,in_last_shift_code
    ,out_last_shift_code
    ,dt
from (
    select * from jms_dm.dm_route_recommend
    where dt='{{ execution_date | cst_ds }}'
    union all
    select * from jms_dm.dm_route_lastest
    where dt='{{ execution_date | cst_ds }}'
) a
group by in_from_code
      ,update_date
      ,in_from_provider_code
      ,in_from_city_code
      ,in_from_area_code
      ,out_to_provider_code
      ,out_to_city_code
      ,out_to_area_code
      ,out_to_code
      ,in_branch_id
      ,main_id
      ,out_branch_id
      ,in_collect_code
      ,in_to_code
      ,start_center_code
      ,search_type
      ,start_network_code
      ,in_from_regional_code
      ,in_from_regional_desc
      ,in_from_financial_center_code
      ,in_from_financial_center_desc
      ,in_from_provider_desc
      ,in_from_city_desc
      ,in_from_area_desc
      ,in_collect_name
      ,in_from_name
      ,in_to_name
      ,in_edge_latest_warehousing
      ,in_edge_planned_departure
      ,in_edge_planned_arrival
      ,in_edge2_planned_departure
      ,in_edge2_planned_arrival
      ,branch_in_span_days
      ,branch_in_total_time
      ,is_main_route
      ,transfer_type
      ,num_of_transfer
      ,whole_route
      ,e1_center_flow
      ,e1_line_code
      ,e1_planned_departure
      ,e1_runtime
      ,e1_planned_arrival
      ,e1_e2_stop_time
      ,e2_center_flow
      ,e2_line_code
      ,e2_planned_departure
      ,e2_runtime
      ,e2_planned_arrival
      ,e2_e3_stop_time
      ,e3_center_flow
      ,e3_line_code
      ,e3_planned_departure
      ,e3_runtime
      ,e3_planned_arrival
      ,e3_e4_stop_time
      ,e4_center_flow
      ,e4_line_code
      ,e4_planned_departure
      ,e4_runtime
      ,e4_planned_arrival
      ,e4_e5_stop_time
      ,e5_center_flow
      ,e5_line_code
      ,e5_planned_departure
      ,e5_runtime
      ,e5_planned_arrival
      ,e5_e6_stop_time
      ,e6_center_flow
      ,e6_line_code
      ,e6_planned_departure
      ,e6_runtime
      ,e6_planned_arrival
      ,e6_e7_stop_time
      ,e1_end_code
      ,e2_end_code
      ,e3_end_code
      ,e4_end_code
      ,e5_end_code
      ,e6_end_code
      ,transfer_total_span_days
      ,transfer_total_time_use
      ,main_total_span_days
      ,main_total_time_use
      ,end_center
      ,end_center_code
      ,start_center
      ,out_from_code
      ,out_collect_code
      ,out_to_regional_desc
      ,out_to_regional_code
      ,out_to_financial_center_desc
      ,out_to_financial_center_code
      ,out_to_provider_desc
      ,out_to_city_desc
      ,out_to_area_desc
      ,out_collect_name
      ,out_to_name
      ,out_from_name
      ,out_has_collect
      ,out_edge_planned_departure
      ,out_edge_planned_arrival_time
      ,out_edge2_planned_departure
      ,out_edge2_planned_arrival_time
      ,out_edge2_latest_warehouse_time
      ,out_edge_latest_warehouse_time
      ,out_edge_span_days_sign
      ,out_edge2_span_days_sign
      ,out_edge_deadline_sign_time
      ,out_edge2_deadline_sign_time
      ,branch_out_span_days
      ,branch_out_total_time
      ,effective_date
      ,expiration_date
      ,branch_in_center_stop_time
      ,center_branch_out_stop_time
      ,branch_in_center_span_days
      ,center_branch_out_span_days
      ,is_circuitous
      ,out_edge_span_days_arrive
      ,out_edge2_span_days_arrive
      ,in_nodes
      ,out_nodes
      ,total_days_use
      ,total_time_use
      ,total_days_t
      ,total_nodes
      ,working_days
      ,extra_in_collect_code
      ,extra_in_collect_name
      ,extra_in_line_name
      ,extra_in_latest_warehousing
      ,extra_in_planned_departure
      ,extra_in_planned_arrival
      ,extra_out_collect_code
      ,extra_out_collect_name
      ,extra_out_line_name
      ,extra_out_latest_warehousing
      ,extra_out_planned_departure
      ,extra_out_planned_arrival
      ,in_network_line_name
      ,in_collect_line_name
      ,out_network_line_name
      ,out_collect_line_name
      ,town_plus_time
      ,branch_in_ship_time
      ,branch_in_ship_span_days
      ,branch_out_ship_time
      ,branch_out_ship_span_days
      ,all_line_name
      ,out_town_plus_time
      ,in_edge_run_time
      ,in_edge_span_days
      ,in_edge2_run_time
      ,in_edge2_span_days
      ,in_edge3_run_time
      ,in_edge3_span_days
      ,out_edge_run_time
      ,out_edge_span_days
      ,out_edge2_run_time
      ,out_edge2_span_days
      ,out_edge3_run_time
      ,out_edge3_span_days
      ,e1_span_days
      ,e2_span_days
      ,e3_span_days
      ,e4_span_days
      ,e5_span_days
      ,e6_span_days
      ,delivery_time
      ,route_flow
      ,in_edge2_latest_warehousing
      ,e1_latest_warehousing
      ,e2_latest_warehousing
      ,e3_latest_warehousing
      ,e4_latest_arrival_time
      ,e5_latest_arrival_time
      ,e6_latest_arrival_time
      ,start_taking_shift
      ,end_send_shift
      ,warehouse_end_time
      ,reserve_1
      ,reserve_2
      ,route_contain_main_line
      ,direct_route_type
      ,in_line_day
      ,out_line_day
      ,in_ship_span_days
      ,in_manage_region_code
      ,in_manage_region_name
      ,out_manage_region_code
      ,out_manage_region_name
      ,latest_warehousing_time_quantum
      ,branch_in_total_all_time
      ,main_total_all_time
      ,branch_out_total_all_time
      ,in_last_shift_code
      ,out_last_shift_code
      ,dt
distribute by pmod(hash(rand()), 100);
